#!/bin/sh

# Sed option for "extended regular expression", may need to change this to -r on non-BSD system
SEDEX="-E"

# Dataset file.
DATASET="EBIRD.2008-2-13.txt"

# Output files.
SCHEMA="schema.sql"
PRIFILE="primary.sql"
SECFILE="secondary.sql"

# Number of rows in dataset according to wc -l. Reduce for testing purposes.
# You will always get one less row than the value in the variable since the
# header counts as one row but is discarded (meta data).
MAXROWS="11085625"

# This command will create a file to list all columns in the input data together with a number.
# Uncomment and run to get information about available columns.
#head -n 1 "$DATASET" | awk -F '\t' '{ n=1; while (length($n)>0) { print n " " $n; n=n+1;} }' > column_names.txt
#exit

# The columns to be put into the primary and the secondary table
PRIMARY="33 34 36 37 38 39 40 43 44 70 71 72 73 74 96 97 102"
SECONDARY="7 9 10 11 12 13 14 15 21 22 23 24 25 26 27 28 42 51 61 62 63 64 65 66 67"

# Create the SQL schema for the primary table, using a default datatype WHICH MUST BE FINE-TUNED!!!
echo "# Schema MUST be fine-tuned, i.e. numeric fields should have numeric types" > $SCHEMA
echo "create table \`dataset_pri\` (
  \`recid\` int not null," >> $SCHEMA
for p in $PRIMARY; do
  echo "  \``head -n 1 "$DATASET" | awk -F '\t' '{ print $'$p'; }' | sed -e 's/ /_/g'`\` varchar(120) null," >> $SCHEMA
done
echo "  primary key(\`recid\`)
);" >> $SCHEMA

# Create the SQL schema for the secondary table, using a default datatype WHICH MUST BE FINE-TUNED!!!
echo "# Schema MUST be fine-tuned, i.e. numeric fields should have numeric types" >> $SCHEMA
echo "create table \`dataset_sec\` (
  \`recid\` int not null," >> $SCHEMA
for p in $SECONDARY; do
  echo "  \``head -n 1 "$DATASET" | awk -F '\t' '{ print $'$p'; }' | sed -e 's/ /_/g'`\` varchar(120) null," >> $SCHEMA
done
echo "  primary key(\`recid\`)
);" >> $SCHEMA

# Generate awk patterns for printing the selected fields quoted and comma-separated (for SQL insert query)
AWKEXPRI="\"'\" `echo \"$PRIMARY\" | sed $SEDEX -e 's/([0-9]+)/$\1/g' | sed -e 's/ / "'"'"','"'"'" /g'` \"'\""
AWKEXSEC="\"'\" `echo \"$SECONDARY\" | sed $SEDEX -e 's/([0-9]+)/$\1/g' | sed -e 's/ / "'"'"','"'"'" /g'` \"'\""

# Extract primary data
head -n $MAXROWS "$DATASET" | awk -F '\t' -v 'n=0' '{if (n==0) { n=1; next; } else {print "INSERT INTO dataset_pri VALUES (" n "," '"$AWKEXPRI"' ");"; n=n+1;}}' | sed -e "s/''/NULL/g" > "$PRIFILE"

# Extract secondary data
head -n $MAXROWS "$DATASET" | awk -F '\t' -v 'n=0' '{if (n==0) { n=1; next; } else {print "INSERT INTO dataset_sec VALUES (" n "," '"$AWKEXSEC"' ");"; n=n+1;}}' | sed -e "s/''/NULL/g" > "$SECFILE"
